﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;

namespace DesignLife.Admin.Business
{
    public class BusinessNews_Category
    {
        SqlConnection con = new SqlConnection();
        SqlCommand com = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();
        private DataTable temp;
        private bool statusAction;
        private SqlDataReader Reader { get; set; }
        private int Success { get; set; }
        
        private SqlDataReader Query(string sql, params object[] parameters)
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["Design_LifeConnection"].ConnectionString;
            com.Connection = con;
            com.CommandText = sql;
            com.CommandType = CommandType.Text;
            var reg = new Regex(@"@\w+");
            MatchCollection ps = reg.Matches(sql);
            if (ps.Count != parameters.Length) throw new Exception("paremeters not valid");
            int i = 0;
            foreach (Match item in ps)
            {
                foreach (Capture capture in item.Captures)
                {
                    com.Parameters.AddWithValue(capture.Value, parameters[i]);
                    i++;
                }
            }
            con.Open();
            return com.ExecuteReader();
        }

        private int Update(string sql, params object[] parameters)
        {
            try
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["Design_LifeConnection"].ConnectionString;
                com.Connection = con;
                com.CommandText = sql;
                com.CommandType = CommandType.Text;
                var reg = new Regex(@"@\w+");
                MatchCollection ps = reg.Matches(sql);
                if (ps.Count != parameters.Length) throw new Exception("paremeters not valid");
                int i = 0;
                foreach (Match item in ps)
                {
                    foreach (Capture capture in item.Captures)
                    {
                        com.Parameters.AddWithValue(capture.Value, parameters[i]);
                        i++;
                    }
                }
                con.Open();
                return com.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return 0;
            }
        }

        public void Close()
        {
            if (Reader != null)
            {
                Reader.Close();
                con.Close();
            }
            if (Success != 0)
            {
                con.Close();
            }
        }

        public DataTable getNews()
        {
            Close();
            temp = new DataTable();
            Reader = Query("SELECT dbo.News.*, dbo.Categories.CategoryName " +
                           "FROM dbo.Categories INNER JOIN " +
                           "dbo.News ON dbo.Categories.CategoryID = dbo.News.IDCategory");
            temp.Load(Reader);
            return temp;
        }


        public DataTable getCate()
        {
            Close();
            temp = new DataTable();
            Reader = Query("SELECT * FROM Categories");
            temp.Load(Reader);
            return temp;
        }

        public DataTable getListCate()
        {
            Close();
            temp = new DataTable();
            Reader = Query("SELECT * FROM Categories where [Status] = 1");
            temp.Load(Reader);
            return temp;
        }

        public bool addNews(string title, string link, string content, DateTime date, int idCate)
        {
            Close();
            object[] param = { title, link, content, date, idCate };
            try
            {
                Success = Update("Insert Into News (Title, Img1, Contents, [DateTime],IDCategory)" +
                                 "values (@title, @img1,@contents, @datetime, @idCategory)", param);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }


        public bool updateNew(string title, string link, string content, DateTime date, int idCate, bool check, int id)
        {
            Close();
            object[] param = { title, link, content, date, idCate, check, id };
            try
            {
                Success = Update("Update News set Title = @title," +
                          "Img1 = @img1, Contents = @contents, DateTime = @datetime, IDCategory = @idCategory, Status = @status where NewsID = @id", param);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool addCate(string ctName, string link, string parrent, string summary)
        {
            Close();
            object[] param = { ctName, link, parrent, summary };
            try
            {
                Success = Update("Insert Into Categories (CategoryName, Link, Parrent, [Summary])" +
                                 "values (@categoryName, @link,@parrent, @summary)", param);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool updateCate(string ctName, string link, string parrent, string summary, bool sttCt, int id)
        {
            Close();
            object[] param = { ctName, link, parrent, summary, sttCt, id };
            try
            {
                Success = Update("Update Categories set CategoryName = @categoryName," +
                          "Link = @link, Parrent = @parrent, Summary = @summary, Status = @status where CategoryID = @id", param);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            
        }
    }
}